package lib_model.Account;

import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import lib_dal.Connection.ConnectMSSQL;
import lib_dal.Interface.ISql;
import lib_model.Orther.ReturnObj;

public class Account implements ISql {
	public String username;
	public String password;
	public String email;
	public int group_id_ref;
	public Date date_create;

	public Account() {
	}
	
	

	public Account(String username) {
		super();
		this.username = username;
	}



	@Override
	public ReturnObj Select() {
		String sql = "select username, group_id_ref, password, email, date_create from dbo.account order by date_create desc";
		ReturnObj obj = new ReturnObj();
		ArrayList<Account> arr = new ArrayList<Account>();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			ResultSet rs = preState.executeQuery();
			while (rs.next()) {
				Account a = new Account();
				a.username = rs.getString("username");
				a.group_id_ref = rs.getInt("group_id_ref");
				a.password = rs.getString("password");
				a.email = rs.getString("email");
				a.date_create = rs.getDate("date_create");
				arr.add(a);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		obj.ObjResult = arr;
		return obj;
	}

	@Override
	public ReturnObj Add() {
		int key = 0; 
		String sql = "insert into dbo.account (username,password,email,group_id_ref, date_create) values   (?,?,?,?,GETDATE())";
		ReturnObj obj = new ReturnObj();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setString(1, this.username);
			preState.setString(2, this.password);
			preState.setString(3, this.email);
			preState.setInt(4, this.group_id_ref);
			obj.Result = (preState.executeUpdate() == 1)? true:false;
			ResultSet keys = preState.getGeneratedKeys();  
			keys.next(); 
			key = keys.getInt(1); 
			keys.close();
			preState.close();
			obj.ObjResult = key;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}
	
	

	@Override
	public ReturnObj Update() {
		String sql = "update dbo.account set password = ?,   email = ?,   group_id_ref = ? where  username = ?";
		ReturnObj obj = new ReturnObj();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setString(1, this.password);
			preState.setString(2, this.email);
			preState.setInt(3, this.group_id_ref);
			preState.setString(4, this.username);
			obj.Result = (preState.executeUpdate() != 0) ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public ReturnObj Delete() {
		String sql = "delete from dbo.account where username = ?";
		ReturnObj obj = new ReturnObj();
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setString(1, this.username);
			obj.Result = preState.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

	@Override
	public ReturnObj GetInformation() {
		ReturnObj obj = new ReturnObj();
		String sql = "select username, group_id_ref, password, email, date_create from dbo.account where username = ?";
		try {
			PreparedStatement preState = new ConnectMSSQL()
					.preparedStatement(sql);
			preState.setString(1, this.username);
			ResultSet rs = preState.executeQuery();
			while (rs.next()) {
				Account a = new Account();
				a.username = rs.getString("username");
				a.group_id_ref = rs.getInt("group_id_ref");
				a.password = rs.getString("password");
				a.email = rs.getString("email");
				a.date_create = rs.getDate("date_create");
				obj.ObjResult = a;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return obj;
	}

}
